﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;

namespace SQLInsert
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            DataTable Dt = new DataTable();
            Dt.Columns.Add("TimeSlot", typeof(DateTime));
            Dt.Columns.Add("UserID", typeof(int));
            Dt.Columns.Add("UserStatus", typeof(string));
            for (int i = 1; i < 1000000; i++)
            {
                DataRow dr = Dt.NewRow();
                dr["TimeSlot"] = DateTime.Now;
                dr["UserID"] = 1;
                dr["UserStatus"] = i % 2 == 0 ? "Y" : "N";
                Dt.Rows.Add(dr);
            }


            
            SqlConnection conn = new SqlConnection("Data Source=IFCA-LIUJU;Initial Catalog=SPSetia_Live;Integrated Security=True");

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            //SqlTransaction tran = conn.BeginTransaction();
            try
            {
                sw.Start();
                //先创建一个表值类型
                string _TempTableSql = @"
CREATE TYPE PS_TimeSlotItemType AS TABLE
(
    TimeSlot datetime,
    UserID int,
    UserStatus varchar(16)
)
";
                SqlCommand comm = new SqlCommand(_TempTableSql, conn);
                comm.ExecuteNonQuery();

                //这里的插入就是我们把创建的表值类型里面做参数.我们把数据放到这个参数里面.然后通过批量插入实现
                //注意,这里的临时表是我们参数指定的
                string sqlStr = @"

insert into PS_Test (TimeSlotID,TimeSlot,UserID,UserStatus,CreateDate,ModifyDate,RecordStatus,CreateUserID,ModifyUserID)
select 7840,a.TimeSlot,a.UserID,a.UserStatus,GETDATE(),GETDATE(),'Active',1,1 from @TempTable as a
";
                SqlCommand InsertComm = new SqlCommand(sqlStr, conn);
                SqlParameter par = InsertComm.Parameters.AddWithValue("@TempTable", Dt);//把我们的表数据放到这个临时的表
                par.SqlDbType = SqlDbType.Structured;
                par.TypeName = "dbo.PS_TimeSlotItemType";//这里的是我们创建的那个表值类型
                InsertComm.ExecuteNonQuery();

                //运行完后.删除这个表值类型
                string deleteTempTableType = "drop type PS_TimeSlotItemType";
                SqlCommand deleteComm = new SqlCommand(deleteTempTableType, conn);
                deleteComm.ExecuteNonQuery();


                //tran.Commit();
                sw.Stop();
                Console.WriteLine(sw.ElapsedMilliseconds);
            }
            catch (Exception ex)
            {
                //tran.Rollback();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                Console.ReadLine();
            }
            
        }
    }
}
